package july.android.cakes.service;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.web.bind.annotation.RequestParam;

@Service
@Transactional
public class SMessage {

    @Autowired
    private JdbcTemplate jdbc;

    //处理上传失物图片
    public void UPlostfound(String pic, String ttype, String detail, String tel, String stu_id) {
        String sql = "insert into lost_message(stu_id,ttype,pic,detail,tel) values(?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbc.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[]{"stu_id", "ttype", "pic", "detail", "tel"});
                ps.setString(1, stu_id);
                ps.setString(2, ttype);
                ps.setString(3, "a");
                ps.setString(4, detail);
                ps.setString(5, tel);
                return ps;
            }
        }, keyHolder);
        int id = keyHolder.getKey().intValue();
        String filename = id + pic.substring(pic.length() - 4);
        
        july.android.cakes.tools.Rename.renameFile(pic.substring(0, pic.length()-19), pic.substring(pic.length()-19), filename);
        filename = "http:" + File.separator + File.separator + "123.206.198.158:8080" + File.separator + "img" + File.separator + "lostfund" + File.separator + filename;;
        this.jdbc.update("update lost_message set pic=? where id=?", filename, id);
    }
    
    //获得所有帖子
    public List<Map<String,Object>>getAllPost(){
        return this.jdbc.queryForList("select lost_message.id,lost_message.stu_id,student.nickname,pic,detail,tel,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date2 from lost_message,student where student.id=lost_message.stu_id ");
    }
    
    //根据传入的帖子号获得帖子对应的评论
    public List<Map<String, Object>> getcomment(String id) {
        return this.jdbc.queryForList("select student.icon,student.nickname,chat,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date from lost_chat,student where student.id=lost_chat.stu_id and p_id=?", id);
    }

    //根据传入的学生学号获取其昵称，若没有昵称，则为姓名
    public String getnickname(String id) {
        if (!(this.jdbc.queryForMap("select nickname from student where id=?", id)).get("nickname").equals("")) {
            return (String) (this.jdbc.queryForMap("select nickname from student where id=?", id)).get("nickname");
        } else {
            return (String) (this.jdbc.queryForMap("select nname from student where id=?", id)).get("nname");
        }
    }

    //根据传入的学生学号获取其头像的路径
    public String getstupic(String id) {
        return (String) (this.jdbc.queryForMap("select icon from student where id=?", id)).get("icon");
    }

    //根据传入的帖子号，评论人号，评论内容往某个特定的帖子插入评论
    public void addcomment(String id, String stu_id, String chat) {
        this.jdbc.update("insert into lost_chat(p_id,stu_id,chat) values(?,?,?)", Integer.parseInt(id), stu_id, chat);
    }

    //往数据库中插入失物招领的信息
    public String addlostfound(String stu_id, String ttype, String detail, String pic, String tel) {
        this.jdbc.update("insert into lost_message(stu_id,ttype,pic,detail,tel) values(?,?,?,?,?)", stu_id, ttype, pic, detail, tel);
        return "true";
    }

    //读出寻物/寻主的所有帖子
    public List<Map<String, Object>> getlostfound(String ttype) {
        return this.jdbc.queryForList("select lost_message.id,student.nickname,student.icon,pic,detail,tel,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date2 from lost_message,student where student.id=lost_message.stu_id and ttype=? order by lost_message.id desc", ttype);
    }

    //根据传入的学生学号获取其发布的所有帖子
    public List<Map<String, Object>> getmessagebyid(String id) {
        return this.jdbc.queryForList("select lost_message.id,student.nickname,pic,detail,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date,tel from lost_message,student where student.id=lost_message.stu_id and lost_message.stu_id=?", id);
    }

    //根据传入的帖子号，帖子内容修改发布的消息
    public void changemessage(String id, String detail) {
        this.jdbc.update("update lost_message set detail=? where id=?", detail, id);
    }

    //根据传入的帖子号删除某条帖子
    public void removemessage(String id) {
        this.jdbc.update("delete from lost_chat where p_id=?", id);
        this.jdbc.update("delete from lost_message where id = ?", id);
        
    }
}
